using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using MediatR;
using Microsoft.Extensions.Logging;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using RBAC.Domain.SalesManage;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Write.Api.Application.Command.ERP.SalesManagement.Return;
using RBAC.Write.Api.Infrastructure;

namespace RBAC.Write.Api.Application.Handler.ERP.SalesManagement.Return
{
    /// <summary>
    /// 导出退货通知单处理器
    /// </summary>
    public class ExportReturnNoticeHandler : IRequestHandler<ExportReturnNoticeCommand, APIResult<byte[]>>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;
        private readonly ILogger<ExportReturnNoticeHandler> _logger;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbConnectionFactory">数据库连接工厂</param>
        /// <param name="logger">日志记录器</param>
        public ExportReturnNoticeHandler(IDbConnectionFactory dbConnectionFactory, ILogger<ExportReturnNoticeHandler> logger)
        {
            _dbConnectionFactory = dbConnectionFactory;
            _logger = logger;
        }

        /// <summary>
        /// 处理导出请求
        /// </summary>
        /// <param name="request">导出命令</param>
        /// <param name="cancellationToken">取消令牌</param>
        /// <returns>Excel文件字节数组</returns>
        public async Task<APIResult<byte[]>> Handle(ExportReturnNoticeCommand request, CancellationToken cancellationToken)
        {
            try
            {
                using var connection = _dbConnectionFactory.CreateConnection();

                // 构建查询条件
                var whereConditions = new List<string>();
                var parameters = new DynamicParameters();

                whereConditions.Add("rn.IsDeleted = 0");

                if (!string.IsNullOrWhiteSpace(request.BillCode))
                {
                    whereConditions.Add("rn.BillCode LIKE @BillCode");
                    parameters.Add("BillCode", $"%{request.BillCode}%");
                }

                if (!string.IsNullOrWhiteSpace(request.CustomerName))
                {
                    whereConditions.Add("rn.CustomerName LIKE @CustomerName");
                    parameters.Add("CustomerName", $"%{request.CustomerName}%");
                }

                if (!string.IsNullOrWhiteSpace(request.SalesPerson))
                {
                    whereConditions.Add("rn.Operator LIKE @SalesPerson");
                    parameters.Add("SalesPerson", $"%{request.SalesPerson}%");
                }

                if (request.BillStatus.HasValue)
                {
                    whereConditions.Add("rn.BillStatus = @BillStatus");
                    parameters.Add("BillStatus", request.BillStatus.Value);
                }

                if (request.BusinessStatus.HasValue)
                {
                    whereConditions.Add("rn.BusinessStatus = @BusinessStatus");
                    parameters.Add("BusinessStatus", request.BusinessStatus.Value);
                }

                if (request.StartDate.HasValue)
                {
                    whereConditions.Add("rn.BillDate >= @StartDate");
                    parameters.Add("StartDate", request.StartDate.Value);
                }

                if (request.EndDate.HasValue)
                {
                    whereConditions.Add("rn.BillDate <= @EndDate");
                    parameters.Add("EndDate", request.EndDate.Value);
                }

                var whereSql = whereConditions.Any() ? $"WHERE {string.Join(" AND ", whereConditions)}" : "";

                // 计算分页
                var offset = (request.PageIndex - 1) * request.PageSize;
                var limitSql = request.ExportAll ? "" : $"OFFSET {offset} ROWS FETCH NEXT {request.PageSize} ROWS ONLY";

                if (request.ExportType == 1)
                {
                    // 单据模式导出
                    return await ExportBillMode(connection, whereSql, parameters, limitSql);
                }
                else
                {
                    // 明细模式导出
                    return await ExportDetailMode(connection, whereSql, parameters, limitSql);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出退货通知单失败");
                return new APIResult<byte[]>
                {
                    Code = APIEnums.Error,
                    Msg = $"导出失败：{ex.Message}"
                };
            }
        }

        /// <summary>
        /// 导出单据模式
        /// </summary>
        private async Task<APIResult<byte[]>> ExportBillMode(IDbConnection connection, string whereSql, DynamicParameters parameters, string limitSql)
        {
            var sql = $@"
                SELECT 
                    rn.BillCode as 单据编码,
                    rn.BillDate as 单据日期,
                    rn.BillStatus as 单据状态,
                    rn.BusinessType as 业务类型,
                    rn.CustomerName as 客户名称,
                    rn.Operator as 销售员,
                    rn.BusinessStatus as 业务状态,
                    rn.ReviewDate as 审核日期,
                    rn.Remark as 备注
                FROM ReturnNotice rn
                {whereSql}
                ORDER BY rn.Id DESC
                {limitSql}";

            var data = await connection.QueryAsync(sql, parameters);

            return CreateExcel(data, "退货通知单");
        }

        /// <summary>
        /// 导出明细模式
        /// </summary>
        private async Task<APIResult<byte[]>> ExportDetailMode(IDbConnection connection, string whereSql, DynamicParameters parameters, string limitSql)
        {
            var sql = $@"
                SELECT 
                    rn.BillCode as 单据编码,
                    rn.BillDate as 单据日期,
                    rn.BillStatus as 单据状态,
                    rn.CustomerName as 客户名称,
                    rm.MaterialCode as 物料编码,
                    rm.MaterialName as 物料名称,
                    rm.BatchNumber as 批号,
                    rm.ReturnQuantity as 退货数量,
                    rm.StockInQuantity as 入库数量,
                    (rm.ReturnQuantity - rm.StockInQuantity) as 未入库数量,
                    rm.Unit as 单位,
                    rm.Remark as 备注
                FROM ReturnNotice rn
                INNER JOIN ReturnMaterials rm ON rn.Id = rm.ReturnNoticeId
                {whereSql}
                ORDER BY rn.Id DESC, rm.Id
                {limitSql}";

            var data = await connection.QueryAsync(sql, parameters);

            return CreateExcel(data, "退货通知单明细");
        }

        /// <summary>
        /// 创建Excel文件
        /// </summary>
        private APIResult<byte[]> CreateExcel(IEnumerable<dynamic> data, string sheetName)
        {
            var workbook = new XSSFWorkbook();
            var sheet = workbook.CreateSheet(sheetName);

            if (!data.Any())
            {
                return new APIResult<byte[]>
                {
                    Code = APIEnums.Error,
                    Msg = "没有可导出的数据"
                };
            }

            var rowIndex = 0;
            var firstRow = data.First();
            var properties = ((IDictionary<string, object>)firstRow).Keys.ToList();

            // 创建表头
            var headerRow = sheet.CreateRow(rowIndex++);
            for (int i = 0; i < properties.Count; i++)
            {
                headerRow.CreateCell(i).SetCellValue(properties[i]);
                var headerCellStyle = workbook.CreateCellStyle();
                var headerFont = workbook.CreateFont();
                headerFont.IsBold = true;
                headerCellStyle.SetFont(headerFont);
                headerRow.GetCell(i).CellStyle = headerCellStyle;
            }

            // 填充数据
            foreach (var item in data)
            {
                var row = sheet.CreateRow(rowIndex++);
                var dict = (IDictionary<string, object>)item;
                for (int i = 0; i < properties.Count; i++)
                {
                    var value = dict[properties[i]];
                    if (value != null)
                    {
                        if (value is DateTime)
                        {
                            row.CreateCell(i).SetCellValue(((DateTime)value).ToString("yyyy-MM-dd"));
                        }
                        else if (value is int || value is long)
                        {
                            row.CreateCell(i).SetCellValue(Convert.ToDouble(value));
                        }
                        else if (value is decimal || value is float || value is double)
                        {
                            row.CreateCell(i).SetCellValue(Convert.ToDouble(value));
                        }
                        else
                        {
                            row.CreateCell(i).SetCellValue(value.ToString());
                        }
                    }
                    else
                    {
                        row.CreateCell(i).SetCellValue("");
                    }
                }
            }

            // 自动调整列宽
            for (int i = 0; i < properties.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }

            using var ms = new MemoryStream();
            workbook.Write(ms);
            var bytes = ms.ToArray();

            return new APIResult<byte[]>
            {
                Code = APIEnums.Success,
                Msg = "导出成功",
                Data = bytes
            };
        }
    }
}